ALTER PROCEDURE sprocGGCRMNoPolicyIssuedSelectItemList
AS

declare @StartDate datetime
declare @EndDate datetime

SET @StartDate = GetDate() - 33
SET @EndDate = GetDate() - 33

Open Symmetric Key CXUserSym
Decryption By Certificate CXUserCert

select D.CustomerType, D.Title, D.CustomerName, D.CarPlate, D.QuoteRefNo, Convert(varchar(50), DECRYPTBYKEY(u.EInfo1)) AS Email, T.ModifiedDate,
CASE WHEN (D.CustomerName NOT Like '%[A-Z,a-z]%') OR (P.NInfo7 = 1) THEN '1'
ELSE '2' END AS LangOption
from tblNonGGTransaction T 
INNER JOIN tblNonGGCarMoreDetails D ON T.RefCEUserID = D.RefCEUserID And T.CECarID = D.CECarID
INNER JOIN tblCEUsrProfile P ON T.RefCEUserID = P.RefCEUserID
INNER JOIN tblCEUser U ON T.RefCEUserID = U.CEID
WHERE Convert(varchar(8), T.ModifiedDate, 112) 
Between Convert(varchar(8), @StartDate, 112) And Convert(varchar(8), @EndDate, 112)
AND T.TransactionTypeID = 200
And T.CECarID NOT IN (Select distinct CECarID from tblNonGGTransaction WHERE Convert(varchar(8), ModifiedDate, 112) 
						Between Convert(varchar(8), @StartDate, 112) And Convert(varchar(8), GETDATE(), 112)
						AND TransactionTypeID > 200)
AND (D.CancelStatus IS NULL OR D.CancelStatus = 0)
--AND (D.CustomerName Like '%[A-Z,a-z]%')
--AND ((D.CustomerName NOT Like '%[A-Z,a-z]%') OR (P.NInfo7 = 1))
Order By T.ModifiedDate ASC

Close Symmetric Key CXUserSym

--select top 10 * from tblNonGGCarMoreDetails 
--Order By ModifiedDate DESC

--select * from tblNonGGTransaction Where QuoteRefNo = 'MTI118GJ'
--delete from tblNonGGTransaction Where TransactionID = 12